﻿/*
*ExcelConfigurature .cs
*
* 功 能： 本地excel表导入到服务器数据库,连接本地数据库和从本地库获得数据
* 类 名： ExcelConfigurature
*
* Ver    变更日期  20130803   负责人  黄永生  变更内容
* ───────────────────────────────────

*/
using System;
using System.Collections.Generic;
using System.Text;
using System.IO;
using System.Data.OleDb;
using System.Data;
using System.Web.UI.WebControls;
using XMK.Tadong.Data;
using System.Collections;


namespace XMK.Tadong.StructuredData_IntoDb.Data
{
   public  class ExcelConfigurature:IConfigurature
   {
        private string conStr = null;
        private OleDbConnection connection = null;
        private DataTable dataTable = new DataTable();
        private List<ArrayList> listArrayList;
        private  string[] restrictStr = new string[4];
        public ExcelConfigurature(string[] confInfo)
        {
            conStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + confInfo[0] + ";Extended Properties=Excel 8.0;";
            connection = new OleDbConnection(conStr);
        }

        #region IConfigurature Members

        // 获得所有数据表名称
        public List<string> GetTableScama()
        {
            dataTable.Columns.Clear();
            List<string> tableLis = new List<string>();
            restrictStr[1] = null;

            try
            {
                connection.Open();
                dataTable = connection.GetSchema("tables", restrictStr);
                DataRowCollection rows = dataTable.Rows;
                foreach (DataRow row in rows)
                {
                    tableLis.Add("[" + row[2].ToString() + "]");
                }

            }
            catch(Exception ex)
            { }
            finally
            {
                connection.Close();
            }
            return tableLis; 
        }

        // 获得所某数据表所有数据列
        public List<string> GetColumnsScama(string tableName)
        {
            StringBuilder sb = new StringBuilder(tableName);
            sb.Remove(0, 1);
            sb.Remove(sb.Length-1,1);
            tableName = sb.ToString();
            dataTable.Columns.Clear();
            List<string> columnLis = new List<string>();
           // string[] restrictStr = new string[2];
            restrictStr[2] = tableName;
            connection.Open();
            try
            {
                dataTable = connection.GetSchema("columns", restrictStr);
                DataRowCollection rows = dataTable.Rows;
                foreach (DataRow row in rows)
                {
                    columnLis.Add(row[3].ToString());
                }
            }
            finally
            {
                connection.Close();
            }
            return columnLis;
        }

         
       /// <summary>
        /// 获得查询结果集
       /// </summary>
       /// <param name="queryStr"></param>
        /// <returns>Tables</returns>
        public System.Data.DataTable GetQueryResultSet(string queryStr)
        {
            DataSet ds = new DataSet();
            OleDbCommand command = new OleDbCommand(queryStr, connection);
            OleDbDataAdapter adapter = new OleDbDataAdapter(command);
            try
            {
                adapter.Fill(ds);
            }
            catch
            {
                JsAlert.Alert("数据库连接错误");
            }
            return ds.Tables[0];
        }
       /// <summary>
        /// 获得查询结果集
       /// </summary>
       /// <param name="queryStr"></param>
        /// <returns>DataSet</returns>
        public System.Data.DataSet GetQueryResultDataSet(string queryStr)
        {
            DataSet ds = new DataSet();
            OleDbCommand command = new OleDbCommand(queryStr, connection);
            OleDbDataAdapter adapter = new OleDbDataAdapter(command);
            try
            {
                adapter.Fill(ds);
            }
            catch
            {
                JsAlert.Alert("数据库连接错误");
            }
            return ds;
        }

        public void SaveConf(GridView dgv)
        {
            throw new Exception("The method or operation is not implemented.");
        }

        public void LoadConf(DropDownList tableSelectBox,GridView dgv)
        {
            throw new Exception("The method or operation is not implemented.");
        }

        /// <summary>
        /// 保存配置信息
        /// </summary>
        /// <param name="laList">字段对应数据</param>
        public void SaveConf(List<ArrayList> laList)
        {
            throw new Exception("The method or operation is not implemented.");
        }

        #endregion
    }
}

